package Demo;

import com.alibaba.druid.pool.DruidDataSource;

import javax.sql.DataSource;
import java.sql.*;
import java.util.List;
import java.util.Properties;
import java.util.Scanner;

/**
 * @author mucd
 * 2021/3/10/010 下午 04:14
 */

public class IImplementation<T> implements IsFunction<T> {
    static DataSource DATA_SOURCE = null;
    static Connection connection = null;
    static Properties properties = null;
    ResultSet resultSet = null;
    Scanner sc = new Scanner(System.in);

    PreparedStatement preparedStatement = null;

    static {
        try {
            Properties properties = new Properties();
            properties.load(IImplementation.class.getClassLoader().getResourceAsStream("2.properties"));
            DATA_SOURCE = new DruidDataSource();
            ((DruidDataSource) DATA_SOURCE).configFromPropety(properties);
        } catch (Exception e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }

    protected void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public int save(T t) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql = "insert into test.per (姓名, 年龄) value (?,?)";
        try {
            connection = DATA_SOURCE.getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);

            System.out.println("设置用户名");
            String scUser = sc.next();
            System.out.println("设置密码");
            String scAge = sc.next();
            preparedStatement.setString(1, scUser);
            preparedStatement.setString(2, scAge);
            int i = preparedStatement.executeUpdate();
            connection.commit();
            System.out.println("数据已提交");
            return i;
        } catch (SQLIntegrityConstraintViolationException e) {
            if (connection != null) {
                try {
                    connection.rollback();
                    System.out.println("添加失败");
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            System.out.println("用户名重复");
        } catch (SQLException e) {
            if (connection != null) {
                System.out.println("添加失败");
                try {
                    connection.rollback();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            System.out.println(e.getMessage());
        } finally {
            closeAll(connection, preparedStatement, resultSet);
        }
        return -1;
    }

    @Override
    public List<T> selectAll() {
        String sql = "select * from test.per";
        try {
            connection = DATA_SOURCE.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String id = resultSet.getString("家庭户号/集体户号");
                String user = resultSet.getString("姓名");
                String age = resultSet.getString("年龄");
                String sex = resultSet.getString("性别");
                System.out.println("id:" + id + " 姓名:" + user + " 性别:" + sex + " 年龄" + age);
            }
            System.out.println("查询完成");
        } catch (SQLException e) {
            System.err.println("查询失败");
            e.printStackTrace();
        } finally {
            closeAll(connection, preparedStatement, resultSet);
        }
        return null;
    }

    @Override
    public int delete(T t) throws SQLException {
        try {
            String sql = "delete from test.per where 姓名 = ?";
            connection = DATA_SOURCE.getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            System.out.println("输入要删除的用户名");
            String delUser = sc.next();
            preparedStatement.setString(1, delUser);
            System.out.println("是否确定删除此用户 是/否");
            String whet = sc.next();
            switch (whet) {
                case "是":
                case "y":
                case "1":
                    int i = preparedStatement.executeUpdate();
                    connection.commit();
                    System.out.println(delUser + " 已被删除");
                    return i;
                case "否":
                case "2":
                case "n":
                    connection.rollback();
                    System.out.println(delUser + " 未删除");
                    return -1;
                default:
                    System.out.println("输入错误");
            }
        } catch (SQLException e) {
            connection.rollback();
            System.out.println("程序错误,数据未作修改");
            e.printStackTrace();
        }
        return 0;
    }


    @Override
    public T selectOne(T t) {
        String sql = "select * from test.per where 姓名 = ?";
        try {
            connection = DATA_SOURCE.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            System.out.println("要查看的姓名");
            String showUser = sc.next();
            preparedStatement.setString(1, showUser);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String getUsername = resultSet.getString("姓名");
                if (showUser.equals(getUsername)) {
                    String getId = resultSet.getString("家庭户号/集体户号");
                    String sex = resultSet.getString("性别");
                    String age = resultSet.getString("年龄");
                    System.out.println("ID:" + getId + " 姓名:" + getUsername + " 性别" + sex +" 年龄:"+age);
                    break;
                } else {
                    System.out.println("没这个人");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int updateUser(T t) {
        String sql = "update test.stu set username= ? where id = ?";
        try {
            connection = DATA_SOURCE.getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);

            System.out.println("输入要修改用户名的ID");
            int updateId = sc.nextInt();
            System.out.println("用户名修改为");
            String updateUser = sc.next();
            System.out.println("正在修改");
            Thread.sleep(200);
            preparedStatement.setString(1, updateUser);
            preparedStatement.setInt(2, updateId);
            int i = preparedStatement.executeUpdate();
            connection.commit();
            System.out.println("修改完成");
            return i;
        } catch (SQLException e) {
            if (connection != null) {
                try {
                    connection.rollback();
                    System.out.println("数据未修改");
                } catch (SQLException throwables) {
                    try {
                        System.out.println("数据未修改");
                        connection.rollback();
                    } catch (SQLException sqlException) {
                        sqlException.printStackTrace();
                    }
                    throwables.printStackTrace();
                }
            }
            e.printStackTrace();
            System.out.println(e.getMessage());
            System.out.println(e.toString());
        } catch (InterruptedException e) {
            if (connection != null) {
                try {
                    connection.rollback();
                    System.out.println("数据未修改");
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            System.out.println(e.getMessage());
            System.out.println(e.toString());
        }
        return -1;
    }

/*    @Override
    public int updatePassWord(T t) throws SQLException {
        try {
            String sql = "select username,password from test.stu where username = ?";
            connection = DATA_SOURCE.getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            System.out.println("输入要修改密码的用户名");
            String upUser = sc.next();
            preparedStatement.setString(1, upUser);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String user = resultSet.getString("username");
                if (upUser.equals(user)) {
                    String passwd = resultSet.getString("password");
                    System.out.println("输入旧密码");
                    String oldPasswd = sc.next();
                    if (oldPasswd.equals(passwd)) {
                        System.out.println("旧密码正确,输入新密码");
                        String newPasswd = sc.next();
                        String upSql = "update test.stu set password = ? where username = ?";
                        preparedStatement = connection.prepareStatement(upSql);
                        preparedStatement.setString(1, newPasswd);
                        preparedStatement.setString(2, upUser);
                        int i = preparedStatement.executeUpdate();
                        connection.commit();
                        System.out.println("修改完成,已提交");
                        return i;
                    } else {
                        System.out.println("旧密码错误");
                    }
                }
            }
        } catch (SQLException e) {
            connection.rollback();
            System.out.println("未提交");
            e.printStackTrace();
        }

        return 0;
    }*/
}

